﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NETMovie.Entities;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace NETMovie.Repositories.MSSQL
{
    public class TransactionRepository : ITransactionRepository
    {
        public Entities.Transaction GetOpenTransaction(string username)
        {
            throw new NotImplementedException();
        }

        public Entities.Transaction CreateTransaction(string username)
        {
            throw new NotImplementedException();
        }

        public bool RentMovie(int transactionID, int movieId)
        {
            throw new NotImplementedException();
        }

        public Entities.MovieRental GetRental(int rentalID)
        {
            throw new NotImplementedException();
        }

        public bool UnrentMovie(int rentalId)
        {
            throw new NotImplementedException();
        }

        public Entities.Transaction GetTransaction(int transactionID)
        {
            throw new NotImplementedException();
        }

        public bool Checkout(int transactionID, decimal amount)
        {
            throw new NotImplementedException();
        }

        public bool ReturnMovie(int rentalID)
        {
            throw new NotImplementedException();
        }

        public Entities.MovieRental[] GetOpenRentals(string username)
        {
            throw new NotImplementedException();
        }

        public Entities.MovieRental[] GetDueRentals(string username)
        {
            throw new NotImplementedException();
        }

        public Entities.MovieRental[] GetActiveRentals(string username)
        {
            throw new NotImplementedException();
        }

        public DataTable GetProfitableUsers(string title, DateTime startDate, DateTime endDate)
        {
            StringBuilder query = new StringBuilder(@"SELECT        u.UserId, u.UserName, COUNT(t.TransactionId) AS NUM_TRANSACTIONS, SUM(t.TotalAmount) AS TOTAL_AMOUNT
                                                        FROM            Transactions AS t INNER JOIN
                                                                                 Users AS u ON t.User_UserId = u.UserId
                                                        WHERE        (t.IsPaid = 'True') AND (t.TotalAmount > 0) ");
            string groupBy = @" GROUP BY u.UserId, u.UserName
                               ORDER BY TOTAL_AMOUNT DESC, NUM_TRANSACTIONS DESC";

            List<SqlParameter> sqlParams = new List<SqlParameter>();
            if (startDate != null)
            {
                query.Append(" AND t.TransactionDate >= @startDate");

                SqlParameter param = new SqlParameter("@startDate", SqlDbType.Date);
                param.Value = startDate;
                sqlParams.Add(param);
            }

            if (endDate != null)
            {
                query.Append(" AND t.TransactionDate <= @endDate");

                SqlParameter param = new SqlParameter("@endDate", SqlDbType.Date);
                param.Value = endDate;
                sqlParams.Add(param);
            }

            query.Append(groupBy);

            DataTable table = new DataTable();
            DataColumn col1 = new DataColumn("UserName", typeof(String));
            DataColumn col2 = new DataColumn("NumTrans", typeof(int));
            DataColumn col3 = new DataColumn("SumAmount", typeof(decimal));

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NETMovieConn"].ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = query.ToString();
                    cmd.Connection = conn;
                    cmd.CommandType = System.Data.CommandType.Text;

                    cmd.Parameters.AddRange(sqlParams.ToArray());

                    SqlDataReader rdr = cmd.ExecuteReader();

                    if (rdr == null || !rdr.HasRows)
                        return null;

                    table.Columns.Add(col1);
                    table.Columns.Add(col2);
                    table.Columns.Add(col3);

                    while (rdr.Read())
                    {
                        DataRow row = table.NewRow();
                        row["UserName"] = rdr["UserName"].ToString();
                        row["NumTrans"] = Convert.ToInt32(rdr["NUM_TRANSACTIONS"].ToString());
                        row["SumAmount"] = Convert.ToDecimal(rdr["TOTAL_AMOUNT"].ToString());

                        table.Rows.Add(row);
                    }

                    
                }

                conn.Close();
            }

            return table;
        }

        public void Dispose()
        {
            return;
        }
    }
}
